package demo;

import utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

class JDBCTest {
    private static final String URL = "jdbc:mysql://192.168.20.133/my_sql_test?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8&autoReconnect=true";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    public static void main(String[] args)  {

        try {
            fun6();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private static void fun6() throws SQLException {
        //2、获取数据库连接
        Connection conn = JDBCUtils.getConnection();
        //3、获取数据库操作对象
        PreparedStatement ps = conn.prepareStatement("select * from emp");
        //4、执行SQL语句
        ResultSet rs = ps.executeQuery();
        List<Emp> empList = new ArrayList<>();
        //5、处理结果集
        while(rs.next()){
            Emp emp = new Emp();
            int empno = rs.getInt("empno");
            String ename = rs.getString("ename");
            String job = rs.getString("job");
            int mgr = rs.getInt("mgr");
            String hiredate = rs.getString("hiredate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptno = rs.getInt("deptno");

            emp.setEmpno(empno);
            emp.setEname(ename);
            emp.setJob(job);
            emp.setMgr(mgr);
            emp.setHiredate(hiredate);
            emp.setSal(sal);
            emp.setComm(comm);
            emp.setDeptno(deptno);

            empList.add(emp);
        }
        empList.forEach(System.out::println);
        //6、释放资源
        JDBCUtils.closeAll(conn,ps,rs);
    }

    private static void fun5() throws ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String uName = sc.nextLine();
        System.out.println("请输入用户密码");
        String uPass = sc.nextLine();

        String SQL = "select * from user where name = ? and password = ?";
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        try(
                // 建立数据库连接
                Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                // 创建Statement对象用于执行预加载SQL语句
                PreparedStatement prepareStatement = connection.prepareStatement(SQL);
        ){
            // 设置参数
            prepareStatement.setString(1,uName);
            prepareStatement.setString(2,uPass);
            // 执行查询
            ResultSet resultSet = prepareStatement.executeQuery();
            // 处理结果集
            if (resultSet.next()){
                System.out.println("有数据");
            }else {
                System.out.println("没有数据");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    private static void fun4() throws ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String uName = sc.nextLine();
        System.out.println("请输入用户密码");
        String uPass = sc.nextLine();

        String SQL = "select * from user where name = '"+uName+"' and password = '"+uPass+"'";
        Class.forName("com.mysql.jdbc.Driver");
        try(
                // 建立数据库连接
                Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                // 创建Statement对象用于执行SQL语句
                Statement statement = connection.createStatement();
                // 执行查询语句，获得结果集
                ResultSet resultSet = statement.executeQuery(SQL);
        ){
            if (resultSet.next()){
                System.out.println("有数据");
            }else {
                System.out.println("没有数据");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void fun3() throws ClassNotFoundException {

        // 创建空的员工列表
        List<Emp> empList = new ArrayList<>();
        // 加载MySQL驱动
        Class.forName("com.mysql.jdbc.Driver");
        try(
                // 建立数据库连接
                Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                // 创建Statement对象用于执行SQL语句
                Statement statement = connection.createStatement();
                // 执行查询语句，获得结果集
                ResultSet resultSet = statement.executeQuery("select * from emp");
        ){

            // 遍历结果集，将每条记录映射为Emp对象，并添加到列表中
            while (resultSet.next()){
                Emp emp = new Emp();
                emp.setEmpno(resultSet.getInt("empno"));
                emp.setEname(resultSet.getString("ename"));
                emp.setJob(resultSet.getString("job"));
                emp.setMgr(resultSet.getInt("mgr"));
                emp.setHiredate(resultSet.getString("hiredate"));
                emp.setSal(resultSet.getDouble("sal"));
                emp.setComm(resultSet.getDouble("comm"));
                emp.setDeptno(resultSet.getInt("deptno"));
                empList.add(emp);

            }
            // 打印所有员工信息
            empList.forEach(System.out::println);

        } catch (SQLException e) {
            // 捕获并抛出SQL异常
            throw new RuntimeException(e);
        }


    }

    private static void fun2() throws ClassNotFoundException, SQLException {
        //加载注册驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        try(
                Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
                Statement statement = connection.createStatement();
        ){
            int i = statement.executeUpdate("insert into emp values (8002,'jackshen','C++',7844,'2020-04-19',1200,300,10)");
            if (i > 0){
                System.out.println("插入成功");
            }else {
                System.out.println("插入失败");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private static void fun1() throws ClassNotFoundException, SQLException {
        //加载驱动程序    通过反射触发类加载实现注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库连接
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        //获取执行sql语句的对象Statement
        Statement statement = connection.createStatement();
        //执行sql语句

        int result = statement.executeUpdate("insert into emp values (8001,'jackma','C#',7844,'2020-03-09',1500,300,20)");
        //判断处理结果
        if (result > 0){
            System.out.println("插入成功");
        }else {
            System.out.println("插入失败");
        }
        //释放资源
        statement.close();
        connection.close();
    }
}
